﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for ConnectionDB
/// </summary>
public class ConnectionDB
{
    SqlConnection cnn;
    SqlDataAdapter da;
    SqlCommand cmd;

    string strCon = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["strCon"].ToString();
    SqlTransaction trans;

    public ConnectionDB()
    {
        cnn = new SqlConnection(strCon);
    }


    //Mở kết nối
    public void Open()
    {
        if (cnn.State == System.Data.ConnectionState.Closed)
            cnn.Open();
    }

    //Đóng kết nối
    private void Close()
    {
        if (cnn.State == System.Data.ConnectionState.Open)
            cnn.Close();
    }

    // Thực thi câu lệnh trả về DataTable
    public DataTable ExecuteQuery(String _sql, SqlParameter[] parameter)
    {
        DataTable dt = new DataTable();
        if (parameter != null)
        {
            cmd = new SqlCommand(_sql, cnn);
            cmd.Parameters.AddRange(parameter);
            da = new SqlDataAdapter(cmd);
            try
            {
                da.Fill(dt);
            }
            catch (SqlException e)
            {
            }
            return dt;
        }
        else
        {
            da = new SqlDataAdapter(_sql, cnn);
            da.Fill(dt);
            return dt;
        }

    }

    //Thực thi các câu lệnh insert update delete
    public Boolean ExecuteNonQuery(String _sql, SqlParameter[] parameter)
    {
        if (parameter != null)
        {
            cmd = new SqlCommand(_sql, cnn);
            try
            {
                Open();
                cmd.Parameters.AddRange(parameter);
                cmd.ExecuteNonQuery();
                Close();
            }
            catch (SqlException e)
            {
                Close();
                return false;
            }
            return true;
        }
        else
        {
            try
            {
                cmd = new SqlCommand(_sql, cnn);
                Open();
                cmd.ExecuteNonQuery();
                Close();

            }
            catch (SqlException e)
            {
                return false;
            }

            return true;
        }
    }

    //Thực thi câu lệnh trả về giá trị đơn
    public object GetValue(string query)
    {
        object result;
        Open();
        cmd = new SqlCommand(query, cnn);
        result = cmd.ExecuteScalar();
        Close();
        return result;
    }


    public DataTable ExecuteProcedure(string sql, SqlParameter[] parameter)
    {

        if (parameter != null)
        {
            cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(parameter);
        }
        else
        {
            cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.StoredProcedure;
        }

        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;

    }

    public int ExecuteNonProcedure(string sql, SqlParameter[] parameter)
    {
        int result;
        if (parameter != null)
        {
            cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(parameter);
        }
        else
        {
            cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.StoredProcedure;
        }
        Open();
        result = cmd.ExecuteNonQuery();
        Close();
        return result;

    }

    public SqlConnection getConnection()
    {
        return this.cnn;
    }
}